package com.xuhuan.demo.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Author huan.xu
 * @Date 2022/6/8 10:14
 * @Description
 */
@Slf4j
public class ExcelUtilDemo {

    @Test
    public void testReadExcel() throws Exception {
        List<Map> excelDataList = ExcelUtil.readExcel(new File("C:\\Users\\huan.xu\\Desktop\\2.xlsx"), 0, 1);
        log.info("excel条数【{}】", excelDataList.size());
    }

    @Test
    public void test1() throws IOException {
        List<Map> excelDataList = ExcelUtil.readExcel(new File("C:\\Users\\huan.xu\\Desktop\\基于需求的开发和测试文档补充-张紫凯.xls"), 0, 2);
        List<Map> userExcelDataList = excelDataList.stream()
                .filter(m -> {
                    return "明文龙".equals(String.valueOf(m.get("cell_6")));
                })
                .collect(Collectors.toList());
        log.info("{}", userExcelDataList.size());
        //String.valueOf(m.get("cell_5") 需求名称
        //String.valueOf(m.get("cell_6") 姓名
        //String.valueOf(m.get("cell_12") 开发设计
        //String.valueOf(m.get("cell_13") 自测
        //String.valueOf(m.get("cell_14") 部署

        FileInputStream file = new FileInputStream("C:\\Users\\huan.xu\\Desktop\\基于需求的开发和测试文档补充-牛树刚2.xls");

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);

        for (Map map : userExcelDataList) {
            int row = (int) map.get("row");
            String cell_12 = String.valueOf(map.get("cell_12"));
            String cell_13 = String.valueOf(map.get("cell_13"));
            String cell_14 = String.valueOf(map.get("cell_14"));

            //开发设计
            ExcelUtil.setCellValue(sheet,row-1,11,cell_12);
            //自测
            ExcelUtil.setCellValue(sheet,row-1,12,cell_13);
            //部署
            ExcelUtil.setCellValue(sheet,row-1,13,cell_14);
        }

        file.close();
        FileOutputStream outFile = new FileOutputStream("C:\\Users\\huan.xu\\Desktop\\基于需求的开发和测试文档补充-牛树刚3.xls");
        workbook.write(outFile);
        outFile.close();

    }


}
